<?php

/**
 * 创建Excel表格
 *
 * @param $filename   string 文件名称
 * @param $heads      array  栏目数据
 * @param $data       array  内容数据
 * @param $syle  int  0.默认
 * @param $title      string 副标题 不传默认为$filename
 * @param $main_title string 主标题 不传默认为 "性福之家"
 * @param $format     array  表格样式参数
 *
 */
function createExcel($filename, $heads, $data, $syle = 0, $title='',$main_title='', $format = array())
{
    require("PHPExcel.php");
    $index = 2;
    $i = 64;
    $objPHPExcel = new PHPExcel();
    $Sheet = $objPHPExcel->setActiveSheetIndex(0);
    $head_arr = array();
    if(!empty($title))
    {
        $index = 3;
        foreach ($heads as $key => $head)
        {
            $i++;
            if($i <= 90)
            {
                $letter = chr($i);
            }
            else
            {
                $j = 65 + $i - 91;
                $letter = "A" . chr($j);
            }
            $head_arr[$letter]['name'] = $key;
            $head_arr[$letter]['rowspan'] = $head['rowspan'];
            $Sheet->setCellValue($letter . "2", $head['name']);
        }
        $head_keys = array_keys($head_arr);
        $Sheet->setCellValue(reset($head_keys) . "1", $title);
        $objPHPExcel->getActiveSheet()->mergeCells(reset($head_keys) . '1:'.end($head_keys) . '1');
    }
    else
    {
        foreach ($heads as $key => $head)
        {
            $i++;
            if($i <= 90)
            {
                $letter = chr($i);
            }
            else
            {
                $j = 65 + $i - 91;
                $letter = "A" . chr($j);
            }
            $head_arr[$letter]['name'] = $key;
            $head_arr[$letter]['rowspan'] = $head['rowspan'];
            $Sheet->setCellValue($letter . "1", $head['name']);
        }

    }

    foreach ($data as $key => $val)
    {
        foreach($head_arr as $letter => $head)
        {
            if($val[$head['name']] > 99999999999 )
            {
                $val[$head['name']] = "'".$val[$head['name']];
            }
            if(strpos($val[$head['name']],'.') && !strpos($val[$head['name']],'%'))
            {
                $objPHPExcel->getActiveSheet()->getStyle("{$letter}{$index}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
            }

            $Sheet->setCellValue("{$letter}{$index}", $val[$head['name']]);
            if (isset($val['rowspan']) && !empty($head['rowspan']))
            {
                $objPHPExcel->getActiveSheet()->mergeCells($letter.$index.':'.$letter.($index+$data[$key]['rowspan']));

            }
        }
        $index++;
    }

    $objPHPExcel->getActiveSheet()->setTitle($filename);
    generateExcel($filename, $objPHPExcel);
}

//导出excel
function generateExcel($filename, $objPHPExcel)
{
    if(strpos($_SERVER["HTTP_USER_AGENT"],"MSIE") || strpos($_SERVER["HTTP_USER_AGENT"],"Trident")){
        $filename = rawurlencode($filename);
    }
    require(ROOT_PATH."/includes/PHPExcel/PHPExcel/Writer/Excel5.php");
    // Redirect output to a client’s web browser (Excel5)
	header("Content-type: text/plain; charset=utf-8");
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
    header('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');

    // If you're serving to IE over SSL, then the following may be needed
    header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
    header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header ('Pragma: public'); // HTTP/1.0

    $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
    $objWriter->save('php://output');
    exit;
}
